package com.hfjyz.registration.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.hfjyz.registration.command.DepartListCommand;
import com.hfjyz.registration.model.City;
import com.hfjyz.registration.model.Depart;
import com.hfjyz.registration.model.Exam;

public class DepartDaoImpl extends BaseDaoImpl<Depart, Integer> implements
		DepartDao {

	protected static String HQL_FOR_FIND_OFFICE = " select " + "     {C.*}, "
			+ "     {D.*}, " + "     {E.*} " + " from " + "     depart D "
			+ "     left join exam E on E.id = D.examId "
			+ "     left join city C on C.id = D.cityId " + " where "
			+ "     1 = 1 ";

	protected DepartDaoImpl() {
		super(Depart.class);
	}

	@Override
	public List<Depart> listAll() throws Exception {
		List<Depart> departs = super.listAll();

		for (Depart depart : departs) {
			if (depart.getCityId() != null) {
				City city = (City) getHibernateTemplate().load(City.class,
						depart.getCityId());
				if (city != null) {
					depart.setCityName(city.getName());
				}
			}
		}
		return departs;
	}

	@SuppressWarnings("unchecked")
	public List<Depart> listByExam(Exam exam) throws Exception {
		List<Depart> departs = getHibernateTemplate().find(
				"from Depart where examId = ? ", exam.getId());
		for (Depart depart : departs) {
			if (depart.getCityId() != null) {
				City city = (City) getHibernateTemplate().load(City.class,
						depart.getCityId());
				if (city != null) {
					depart.setCityName(city.getName());
				}
			}
		}
		return departs;
	}
	
	@SuppressWarnings("unchecked")
	public List<Depart> getDepartById(String examId) throws Exception {
		List<Depart> departs = getHibernateTemplate().find(
				"from Depart where examId = ? ", Integer.valueOf(examId));

		return departs;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Depart> search(final DepartListCommand cmd) throws Exception {
		return (List<Depart>) getHibernateTemplate().execute(
				new HibernateCallback() {

					@SuppressWarnings("rawtypes")
					@Override
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = HQL_FOR_FIND_OFFICE;

						List<Object> paramList = new ArrayList<Object>();
						if (cmd.getExamId() != null && cmd.getExamId() >= 0) {
							hql += "and E.id=? ";
							paramList.add(cmd.getExamId());
						}
						if (cmd.getDid() != null && cmd.getDid() >= 0) {
							hql += "and D.id=? ";
							paramList.add(cmd.getDid());
						}
						if (cmd.getName() != null
								&& cmd.getName().trim().length() > 0) {
							hql += "and D.name LIKE ? ";
							paramList.add(getLikeStr(cmd.getName().trim()));
						}
						hql += " ORDER BY E.id, D.id ";
						SQLQuery sq = session.createSQLQuery(hql);
						sq.addEntity("D", Depart.class);
						sq.addEntity("C", City.class);
						sq.addEntity("E", Exam.class);
						sq.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
						for (int i = 0; i < paramList.size(); i++) {
							sq.setParameter(i, paramList.get(i));
						}
						List<Depart> offices = new ArrayList<Depart>();
						List<?> list = sq.list();
						for (Object o : list) {
							Map map = (Map) o;
							Depart dept = (Depart) map.get("D");
							City city = (City) map.get("C");
							Exam exam = (Exam) map.get("E");
							if (city != null) {
								dept.setCityName(city.getName());
							}
							if (exam != null) {
								dept.setExamId(exam.getId());
								dept.setExamName(exam.getName());
							}
							offices.add(dept);
						}
						return offices;
					}
				});

	}

	private String getLikeStr(String target) {
		if (target == null) {
			return null;
		}
		return "%" + target.replaceAll("%", "%%") + "%";
	}

}
